{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "import os "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import shapiro, ttest_ind, mannwhitneyu, levene\n",
    "import scipy.stats as stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "warnings.simplefilter(action='ignore', category=FutureWarning)\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.options.display.float_format = '{:.4f}'.format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(\"cookie_cats.csv\")\n",
    "data1 = data[data.sum_gamerounds < data.sum_gamerounds.max()]\n",
    "data1['retention'] = np.where((data1.retention_1 == True) & (data1.retention_7 == True), 1, 0) \n",
    "data1['NewRetention'] = list(map(lambda x,y : str(x) + '-' + str(y), data1.retention_1, data1.retention_7))\n",
    "data1['version']=np.where(data1.version == 'gate_30', 'A', 'B')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "data3 = data1.copy()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userid</th>\n",
       "      <th>version</th>\n",
       "      <th>sum_gamerounds</th>\n",
       "      <th>retention_1</th>\n",
       "      <th>retention_7</th>\n",
       "      <th>retention</th>\n",
       "      <th>NewRetention</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>116</td>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>False-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>337</td>\n",
       "      <td>A</td>\n",
       "      <td>38</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>True-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>377</td>\n",
       "      <td>B</td>\n",
       "      <td>165</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>True-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>483</td>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>False-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>488</td>\n",
       "      <td>B</td>\n",
       "      <td>179</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>True-True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userid version  sum_gamerounds  retention_1  retention_7  retention  \\\n",
       "0     116       A               3        False        False          0   \n",
       "1     337       A              38         True        False          0   \n",
       "2     377       B             165         True        False          0   \n",
       "3     483       B               1        False        False          0   \n",
       "4     488       B             179         True         True          1   \n",
       "\n",
       "  NewRetention  \n",
       "0  False-False  \n",
       "1   True-False  \n",
       "2   True-False  \n",
       "3  False-False  \n",
       "4    True-True  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userid</th>\n",
       "      <th>version</th>\n",
       "      <th>sum_gamerounds</th>\n",
       "      <th>retention_1</th>\n",
       "      <th>retention_7</th>\n",
       "      <th>retention</th>\n",
       "      <th>NewRetention</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>116</td>\n",
       "      <td>A</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>337</td>\n",
       "      <td>A</td>\n",
       "      <td>38</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>True-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>377</td>\n",
       "      <td>B</td>\n",
       "      <td>165</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>True-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>483</td>\n",
       "      <td>B</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>488</td>\n",
       "      <td>B</td>\n",
       "      <td>179</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>True-True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90183</th>\n",
       "      <td>9999441</td>\n",
       "      <td>B</td>\n",
       "      <td>97</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>True-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90184</th>\n",
       "      <td>9999479</td>\n",
       "      <td>B</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90185</th>\n",
       "      <td>9999710</td>\n",
       "      <td>A</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>True-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90186</th>\n",
       "      <td>9999768</td>\n",
       "      <td>B</td>\n",
       "      <td>51</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>True-False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90187</th>\n",
       "      <td>9999861</td>\n",
       "      <td>B</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>False-False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90188 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        userid version  sum_gamerounds  retention_1  retention_7  retention  \\\n",
       "0          116       A               3            0            0          0   \n",
       "1          337       A              38            1            0          0   \n",
       "2          377       B             165            1            0          0   \n",
       "3          483       B               1            0            0          0   \n",
       "4          488       B             179            1            1          1   \n",
       "...        ...     ...             ...          ...          ...        ...   \n",
       "90183  9999441       B              97            1            0          0   \n",
       "90184  9999479       B              30            0            0          0   \n",
       "90185  9999710       A              28            1            0          0   \n",
       "90186  9999768       B              51            1            0          0   \n",
       "90187  9999861       B              16            0            0          0   \n",
       "\n",
       "      NewRetention  \n",
       "0      False-False  \n",
       "1       True-False  \n",
       "2       True-False  \n",
       "3      False-False  \n",
       "4        True-True  \n",
       "...            ...  \n",
       "90183   True-False  \n",
       "90184  False-False  \n",
       "90185   True-False  \n",
       "90186   True-False  \n",
       "90187  False-False  \n",
       "\n",
       "[90188 rows x 7 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = sqlite3.connect('abtest.db')\n",
    "\n",
    "data3.to_sql('abtest', conn, if_exists='replace', index=False)\n",
    "\n",
    "query = 'SELECT * FROM abtest'\n",
    "d3_from_db = pd.read_sql(query, conn)\n",
    "\n",
    "d3_from_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>label</th>\n",
       "      <th>version</th>\n",
       "      <th>cnts</th>\n",
       "      <th>retent_cnts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>retention_1</td>\n",
       "      <td>A</td>\n",
       "      <td>44699</td>\n",
       "      <td>20034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>retention_1</td>\n",
       "      <td>B</td>\n",
       "      <td>45489</td>\n",
       "      <td>20119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>retention_7</td>\n",
       "      <td>A</td>\n",
       "      <td>44699</td>\n",
       "      <td>8501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>retention_7</td>\n",
       "      <td>B</td>\n",
       "      <td>45489</td>\n",
       "      <td>8279</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         label version   cnts  retent_cnts\n",
       "0  retention_1       A  44699        20034\n",
       "1  retention_1       B  45489        20119\n",
       "2  retention_7       A  44699         8501\n",
       "3  retention_7       B  45489         8279"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = '''\n",
    "    SELECT label, version, SUM(cnt) as cnts, SUM(is_retent) as retent_cnts\n",
    "    FROM (SELECT 'retention_1' as label, version, userid, 1 as cnt, retention_1 as is_retent\n",
    "            FROM abtest\n",
    "            UNION ALL\n",
    "            SELECT 'retention_7' as label, version, userid, 1 as cnt, retention_7 as is_retent\n",
    "            FROM abtest) AS subquery\n",
    "    GROUP BY label, version;\n",
    "'''\n",
    "d3_from_db = pd.read_sql(query, conn)\n",
    "\n",
    "d3_from_db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "from statsmodels.stats.proportion import proportions_ztest\n",
    "\n",
    "# dt 必须是数据框形式，共四列，分别为metrics，group，population，target，分别代表变量名称，组别，样本量，命中样本量\n",
    "# base 为基准组，在group中选择一个座位基准，其他各组将与该组进行双样本T test\n",
    "# base 取值方式：base = ‘该组名字’\n",
    "def binary_t_test(dt = None, base_group = None):\n",
    "    # rename the columns\n",
    "    dt.columns = ['metrics', 'group', 'population', 'target']\n",
    "\n",
    "    # obtain the unique metics and group\n",
    "    metrics_vec = dt['metrics'].unique()\n",
    "    group_vec = dt['group'].unique()\n",
    "\n",
    "    p_values = pd.DataFrame(index=metrics_vec, columns=group_vec)\n",
    "\n",
    "    for i, metric in enumerate(metrics_vec):\n",
    "        # 从数据框中选择当前指标的字集\n",
    "        sub_tt = dt[dt['metrics'] == metric]\n",
    "        p_value = np.zeros(len(group_vec))\n",
    "\n",
    "        for j, group in enumerate(group_vec):\n",
    "            # 基准组的样本量和命中样本量\n",
    "            pop0 = sub_tt.loc[sub_tt['group'] == base_group, 'population'].values[0]\n",
    "            tar0 = sub_tt.loc[sub_tt['group'] == base_group, 'target'].values[0]\n",
    "\n",
    "            # 当前组的样本量和命中样本量\n",
    "            pop1 = sub_tt.loc[sub_tt['group'] == group, 'population'].values[0]\n",
    "            tar1 = sub_tt.loc[sub_tt['group'] == group, 'target'].values[0]\n",
    "            \n",
    "            count = np.array([tar0, tar1])\n",
    "            nobs = np.array([pop0, pop1])\n",
    "\n",
    "            _, p_value[j] = proportions_ztest(count, nobs, alternative='two-sided')\n",
    "        \n",
    "        p_values.loc[metric, :] = p_value\n",
    "    \n",
    "    result = {'p value' : p_values}\n",
    "    return result\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'p value':                  A      B\n",
       " retention_1 1.0000 0.0739\n",
       " retention_7 1.0000 0.0016}"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 在retention_7 更显著\n",
    "binary_t_test(dt=d3_from_db, base_group='A')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Retention 1 - day, delta = -0.005915196587034155, se = 0.0033099350735518005, z = -1.787103509763628, p = 0.0739207603418346\n",
      "Retention 7 - day, delta = -0.00818318160884296, se = 0.0025917386041911824, z = -3.1574100858819936, p = 0.0015917731773993442\n"
     ]
    }
   ],
   "source": [
    "#计算对照组和实验组的留存率\n",
    "retention_1_A = data3[data3['version'] == 'A']['retention_1'].mean()\n",
    "retention_1_B = data3[data3['version'] == 'B']['retention_1'].mean()\n",
    "retention_7_A = data3[data3['version'] == 'A']['retention_7'].mean()\n",
    "retention_7_B = data3[data3['version'] == 'B']['retention_7'].mean()\n",
    "\n",
    "# 计算留存率的差异 （Delta）\n",
    "delta_1 = retention_1_B - retention_1_A\n",
    "delta_7 = retention_7_B - retention_7_A\n",
    "\n",
    "# 计算p1，p2，p等指标\n",
    "n_A = data3[data3['version'] == 'A'].shape[0]\n",
    "n_B = data3[data3['version'] == 'B'].shape[0]\n",
    "\n",
    "retention_1_p1 = retention_1_A\n",
    "retention_1_p2 = retention_1_B\n",
    "retention_7_p1 = retention_7_A\n",
    "retention_7_p2 = retention_7_B\n",
    "\n",
    "retention_1_p = (retention_1_p1*n_A + retention_1_p2*n_B) / (n_A + n_B)\n",
    "retention_7_p = (retention_7_p1*n_A + retention_7_p2*n_B) / (n_A + n_B)\n",
    "\n",
    "se_1 = np.sqrt((retention_1_p*(1-retention_1_p) / n_A) + (retention_1_p*(1-retention_1_p) / n_B))\n",
    "se_7 = np.sqrt((retention_7_p*(1-retention_7_p) / n_A) + (retention_7_p*(1-retention_7_p) / n_B))\n",
    "\n",
    "# z value\n",
    "z_1 = delta_1 / se_1\n",
    "z_7 = delta_7 / se_7\n",
    "\n",
    "# p value\n",
    "p_1 = stats.norm.sf(abs(z_1)) * 2\n",
    "p_7 = stats.norm.sf(abs(z_7)) * 2\n",
    "\n",
    "print(f'Retention 1 - day, delta = {delta_1}, se = {se_1}, z = {z_1}, p = {p_1}')\n",
    "print(f'Retention 7 - day, delta = {delta_7}, se = {se_7}, z = {z_7}, p = {p_7}')\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "retention 1 day difference is NOT significant.\n",
      "retention 7 day difference is significant.\n"
     ]
    }
   ],
   "source": [
    "# 判断显著性\n",
    "alpha = 0.05\n",
    "if p_1 < alpha:\n",
    "    print('retention 1 day difference is significant.')\n",
    "else:\n",
    "    print('retention 1 day difference is NOT significant.')\n",
    "\n",
    "if p_7 < alpha:\n",
    "    print('retention 7 day difference is significant.')\n",
    "else:\n",
    "    print('retention 7 day difference is NOT significant.')\n",
    "    \n"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "9fb1d59132b473f3e61b55829eb790bf3bc537ee4092453a0aecbd08ee45a03d"
  },
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
